/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cbsrs.controller;

import com.cbsrs.model.Users;
import com.cbsrs.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.Calendar;
import java.util.logging.Level;
import org.apache.log4j.Logger;

/**
 *
 * @author Arnold
 */
public class StudentDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(StudentDAO.class);

    /**
     * *
     * This is use in saving newly registered Student.
     *
     * @param studentPassword
     * @param studentEmail
     * @param collegeID
     * @param programRevisionID
     * @param studentFirstName
     * @param studentMiddleName
     * @param studentLastName
     * @param studentAddress
     * @param studentDateOfBirth
     * @param studentPlaceOfBirth
     * @param studentContactNumber
     * @param studentYear
     * @param studentStatus
     * @return 1 if successful else 0
     */
    public static String register(String studentPassword, String studentEmail, int collegeID, int programRevisionID, String studentFirstName, String studentMiddleName, String studentLastName, String studentDateOfBirth, String studentPlaceOfBirth, String studentContactNumber, String studentAddress) {
        log.info("Student Address :" + studentAddress);
        String studentIDNumber = "";
        int studentID = getStudentID(studentFirstName, studentMiddleName, studentLastName);
        try {
            if (studentID == 0) {
                String sql = null;

                sql = "INSERT INTO students (student_password, student_email, college_id, program_revision_id, \n"
                        + "	student_first_name, student_middle_name, student_last_name, student_date_of_birth, \n"
                        + "	student_place_of_birth, student_contact_no,student_address)\n"
                        + "	values(?,?,?,?,?,?,?,str_to_date(?,'%m/%d/%Y'),?,?,?)";

                PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
                preparedStatement.setString(1, studentPassword);
                preparedStatement.setString(2, studentEmail);
                preparedStatement.setInt(3, collegeID);
                preparedStatement.setInt(4, programRevisionID);
                preparedStatement.setString(5, studentFirstName);
                preparedStatement.setString(6, studentMiddleName);
                preparedStatement.setString(7, studentLastName);
                preparedStatement.setString(8, studentDateOfBirth);
                preparedStatement.setString(9, studentPlaceOfBirth);
                preparedStatement.setString(10, studentContactNumber);
                preparedStatement.setString(11, studentAddress);

                if (preparedStatement.executeUpdate() > 0) {

                    studentID = getStudentID(studentFirstName, studentMiddleName, studentLastName);
                    if (studentID > 0) {
                        NumberFormat formatter = new DecimalFormat("00000");
                        String year = String.valueOf(Calendar.getInstance().get(Calendar.YEAR));
                        log.info("YEAR :" + year);
                        log.info("STUDENT ID:" + formatter.format(studentID));
                        studentIDNumber = setStudentIDNumber(studentID, year.substring(2) + "-" + formatter.format(studentID));
                    }
                }

                log.info("REGISTER USER: " + preparedStatement.toString());

            } else {
                studentIDNumber = getStudentIDNumber(studentID);
            }
        } catch (SQLException ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        log.info("STUDENT ID Number :" + studentIDNumber);
        return studentIDNumber;
    }

    public static int getStudentID(String studentFirstName, String studentMiddleName, String studentLastName) {
        String sql = null;
        int studentID = 0;
        PreparedStatement preparedStatement;
        try {
            sql = "SELECT student_id from students where student_first_name=? AND student_middle_name=? AND student_last_name=?";
            preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, studentFirstName);
            preparedStatement.setString(2, studentMiddleName);
            preparedStatement.setString(3, studentLastName);
            log.info(preparedStatement.toString());
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                studentID = rs.getInt("student_id");
            }
//            }

            log.info("studentID : " + studentID);
        } catch (SQLException ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return studentID;
    }

    public static String getStudentIDNumber(int studentID) {
        String sql;
        String studentIDNumber = "";
        PreparedStatement preparedStatement;
        try {
            sql = "SELECT student_idno from students where student_id=?";
            preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, studentID);
            log.info(preparedStatement.toString());
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                studentIDNumber = rs.getString("student_idno");
            }
//            }

            log.info("studentIDNo : " + studentIDNumber);
        } catch (SQLException ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return studentIDNumber;
    }

    public static String setStudentIDNumber(int studentID, String IDNumber) {
        String sql;
        String studentIDNumber = "";
        PreparedStatement preparedStatement;
        try {
            sql = "UPDATE students SET student_idno=? WHERE student_id=?";
            preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, IDNumber);
            preparedStatement.setInt(2, studentID);
            log.info(preparedStatement.toString());
            if (preparedStatement.executeUpdate() > 0) {
                studentIDNumber = IDNumber;
            }
//            }

            log.info("studentIDNo : " + studentIDNumber);
        } catch (SQLException ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        } finally {
            closeConnection(getConnectionObject());
        }
        return studentIDNumber;
    }

    
}
